//
//  DataBase.m
//  豆瓣_精简版
//
//  Created by dllo on 15/11/7.
//  Copyright © 2015年 蓝鸥科技. All rights reserved.
//

#import "DataBase.h"

@implementation DataBase

+ (instancetype)shareDataBase{
    
    static DataBase *dataBase = nil;
    if (dataBase == nil) {
        dataBase = [[DataBase alloc]init];
    }
    return dataBase;
}

-(void)openDB
{
    //导入sqlite框架，导入FMDB文件夹
    
    //1.获得数据库文件的路径
    NSString *fileName=[DOCUMENTPATH stringByAppendingPathComponent:@"douban.sqlite"];
    NSLog(@"fileName = %@",fileName);
    
    //2.获得数据库
    self.db = [FMDatabase databaseWithPath:fileName];
    
    //3.打开数据库
    if ([self.db open]) {
        NSLog(@"打开成功");
    }else{
        NSLog(@"打开失败");
    }
}

//创建movie表
- (void)createMovieTable{
    
    BOOL result=[self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS movie (number INTEGER PRIMARY KEY AUTOINCREMENT, userName TEXT NOT NULL, movieName TEXT NOT NULL, movieId TEXT NOT NULL, pic_url TEXT NOT NULL);"];
    
    if (result) {
        NSLog(@"movie创表成功");
    }else{
        NSLog(@"movie创表失败");
    }
}

//创建movie表
- (void)createHuanCunTable{
    
    BOOL result=[self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS movieHuanCun (number INTEGER PRIMARY KEY AUTOINCREMENT, userName TEXT NOT NULL, movieName TEXT NOT NULL, movieId TEXT NOT NULL, pic_url TEXT NOT NULL);"];
    
    if (result) {
        NSLog(@"movie创表成功");
    }else{
        NSLog(@"movie创表失败");
    }
}

//创建user表
- (void)createUserTable{
    
    BOOL result=[self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS user (number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, password TEXT NOT NULL);"];
    
    if (result) {
        NSLog(@"user创表成功");
    }else{
        NSLog(@"user创表失败");
    }
}

#warning mark - 插入数据
-(void)insertMovieData:(Movie *)movie{
    //如果用stringWithFormat时,sql语句中可以用'%@'
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO movie (userName, movieName, movieId, pic_url) VALUES ('%@', '%@', '%@', '%@')", movie.userName, movie.movieName, movie.movieId, movie.pic_url];
    BOOL result = [self.db executeUpdate:sql];
    if (result) {
        NSLog(@"插入movie成功");
    }else{
        NSLog(@"插入movie失败");
    }
}

-(void)insertMovieDetailData:(Movie *)movie{
    //如果用stringWithFormat时,sql语句中可以用'%@'
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO movieHuanCun (userName, movieName, movieId, pic_url) VALUES ('%@', '%@', '%@', '%@')", movie.userName, movie.movieName, movie.movieId, movie.pic_url];
    BOOL result = [self.db executeUpdate:sql];
    if (result) {
        NSLog(@"插入movie成功");
    }else{
        NSLog(@"插入movie失败");
    }
}

//插入数据
- (void)insertUser:(NSString *)name password:(NSString *)password{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO user(name, password) VALUES('%@', '%@')", name, password];
    BOOL result = [self.db executeUpdate:sql];
    if (result) {
        NSLog(@"插入user成功");
    }else{
        NSLog(@"插入user失败");
    }
}

#warning mark - 删除单行数据
-(void)deleteData:(NSString *)movieName{
    
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM movie WHERE movieName = '%@'",movieName];
    BOOL res = [self.db executeUpdate:deleteSql];
    if (res) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
}

#warning mark - 销毁表格
-(void)dropTable:(NSString *)tableName{
    
    NSString *sql = [NSString stringWithFormat:@"DROP TABLE IF EXISTS '%@'",tableName];
    BOOL result = [self.db executeUpdate:sql];
    if (result) {
        NSLog(@"删除%@表成功",tableName);
    }else{
        NSLog(@"删除%@表失败",tableName);
    }
}

#warning mark - 查询数据
-(NSMutableArray *)selectMovieData:(NSString *)userName{
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:[NSString stringWithFormat:@"select * from movie WHERE movieName = '%@'",userName]];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        NSInteger number = [resultSet intForColumn:@"number"];
        NSString *userName = [resultSet objectForColumnName:@"userName"];
        NSString *movieName = [resultSet objectForColumnName:@"movieName"];
        NSString *movieId = [resultSet objectForColumnName:@"movieId"];
        NSString *pic_url = [resultSet objectForColumnName:@"pic_url"];
        
        Movie *movie = [[Movie alloc]init];
        movie.number = number;
        movie.userName = userName;
        movie.movieName = movieName;
        movie.movieId = movieId;
        movie.pic_url = pic_url;
        
        [array addObject:movie];
    }
    return array;
}

-(NSMutableArray *)selectMovieUserData:(NSString *)userName movieName:(NSString *)movieName{
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:[NSString stringWithFormat:@"select * from movie WHERE userName = '%@' AND movieName = '%@'",userName,movieName]];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        NSInteger number = [resultSet intForColumn:@"number"];
        NSString *userName = [resultSet objectForColumnName:@"userName"];
        NSString *movieName = [resultSet objectForColumnName:@"movieName"];
        NSString *movieId = [resultSet objectForColumnName:@"movieId"];
        NSString *pic_url = [resultSet objectForColumnName:@"pic_url"];
        
        Movie *movie = [[Movie alloc]init];
        movie.number = number;
        movie.userName = userName;
        movie.movieName = movieName;
        movie.movieId = movieId;
        movie.pic_url = pic_url;
        
        [array addObject:movie];
    }
    return array;
}

-(NSMutableArray *)selectMovieUserData:(NSString *)userName{
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:[NSString stringWithFormat:@"select * from movie WHERE userName = '%@'",userName]];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        NSInteger number = [resultSet intForColumn:@"number"];
        NSString *userName = [resultSet objectForColumnName:@"userName"];
        NSString *movieName = [resultSet objectForColumnName:@"movieName"];
        NSString *movieId = [resultSet objectForColumnName:@"movieId"];
        NSString *pic_url = [resultSet objectForColumnName:@"pic_url"];
        
        Movie *movie = [[Movie alloc]init];
        movie.number = number;
        movie.userName = userName;
        movie.movieName = movieName;
        movie.movieId = movieId;
        movie.pic_url = pic_url;
        
        [array addObject:movie];
    }
    return array;
}

-(NSMutableArray *)selectMovieDetailData{
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from movieHuanCun"];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        NSInteger number = [resultSet intForColumn:@"number"];
        NSString *userName = [resultSet objectForColumnName:@"userName"];
        NSString *movieName = [resultSet objectForColumnName:@"movieName"];
        NSString *movieId = [resultSet objectForColumnName:@"movieId"];
        NSString *pic_url = [resultSet objectForColumnName:@"pic_url"];
        
        Movie *movie = [[Movie alloc]init];
        movie.number = number;
        movie.userName = userName;
        movie.movieName = movieName;
        movie.movieId = movieId;
        movie.pic_url = pic_url;
        
        [array addObject:movie];
    }
    return array;
}



-(NSMutableArray *)selectUserData:(NSString *)name1{
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:[NSString stringWithFormat:@"select * from user WHERE name = '%@'", name1]];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        NSInteger number = [resultSet intForColumn:@"number"];
        NSString *name = [resultSet objectForColumnName:@"name"];
        NSString *password = [resultSet objectForColumnName:@"password"];
        
        User *user = [[User alloc]init];
        user.number = number;
        user.name = name;
        user.password = password;
        
        [array addObject:user];
    }
    return array;
}

@end
